retrieve data based on date range using mysql ,php [on hold]

Posted by preethi on Programmers See other posts from Programmers or by preethi
Published on 2013-10-21T13:00:30Z Indexed on 2013/10/21 16:09 UTC
Read the original article Hit count: 168

Filed under:

I am working on WPF where I have two datepickers when I try to retrieve the information on date range it displays only one record on all dates(same record displaying multiple times eg : date chosen from 01/10/2013 - 3/10/2013) where I have 3 different records on each day but my output is the first record displayed 3 times with same date and time.

function cpWhitelistStats() {
    $startDate = $_POST['startDate'];
    $startDateTime = "$startDate 00:00:00";
    $endDate = $_POST['endDate'];
    $endDateTime = "$endDate 23:59:59";
    $cpId = $_POST['id'];
    $cpName = etCommonCpNameById($cpId);

    print "<h2 style=\"text-align: center;\">Permitted Vehicle Summary</h2>";
    print "<h2 style=\"text-align: center;\">for $cpName</h2>";
    $tmpDate = explode("/", $startDate);
    $startYear = $tmpDate[2];
    $startMonth= $tmpDate[1];
    $startDay = $tmpDate[0];
    $tmpDate = explode("/", $endDate);
    $endYear = $tmpDate[2];
    $endMonth= $tmpDate[1];
    $endDay = $tmpDate[0];

    $startDateTime = "$startYear-$startMonth-$startDay 00:00:00";
    $endDateTime = "$endYear-$endMonth-$endDay 23:59:59";

    $custId = $_SESSION['customerID'];
    $realCustomerId = $_SESSION['realCustomerId'];

    $maxVal = 0;

    if ($custId != "") {
        $conn = &newEtConn($custId);
        // Get the whitelist plates
        $staticWhitelistArray = etCommonMkWhitelist($conn, $cpId);
        array_shift($staticWhitelistArray);

        $startLoopDate = strtotime($startDateTime);
        $endLoopDate = strtotime($endDateTime);
        $oneDay = 60 * 60 * 24;

        // Get the entries
        $plateList = array_keys($staticWhitelistArray);
        $plate_lookup = implode('","', $plateList);
        $sql = "SELECT plate, entry_datetime, exit_datetime FROM stats WHERE plate IN (\"$plate_lookup\") AND entry_datetime > \"$startDateTime\" AND entry_datetime < \"$endDateTime\" AND carpark_id=\"$cpId\" ";
        $result = $conn->Execute($sql);
        if (!$result) {
            print $conn->ErrorMsg();
            exit;
        }
        $rows = $result->fields;
        if ($rows != "") {
            unset($myArray);
            foreach($result as $values) {
                $plate = $values['plate'];
                $new_platelist[] = $plate;
                $inDateTime = $values['entry_datetime'];
                $outDateTime = $values['exit_datetime'];
                $tmp = explode(' ', $inDateTime);
                $inDate = $tmp[0];
                $in_ts = strtotime($inDateTime);
                $out_ts = strtotime($outDateTime);
                $duration = $out_ts - $in_ts;
                $dur_array = intToDateArray($duration);
                $dur_string = '';
                if ($dur_array['days'] > 0) {
                    $dur_string .= $dur_array['days'] . ' days ';
                }
                if ($dur_array['hours'] > 0) {
                    $dur_string .= $dur_array['hours'] . ' hours ';
                }
                if ($dur_array['mins'] > 0) {
                    $dur_string .= $dur_array['mins'] . ' minutes ';
                }
                if ($dur_array['secs'] > 0) {
                    $dur_string .= $dur_array['secs'] . ' secs ';
                }
                $myArray[$plate][] = array($inDateTime, $outDateTime, $inDate, $dur_string);
            }
        }

        while ($startLoopDate < $endLoopDate) {
            $dayString = strftime("%a, %d %B %Y", $startLoopDate);
            $dayCheck = strftime("%Y-%m-%d", $startLoopDate);

            print "<h2>$dayString</h2>";
            print "<table width=\"100%\">";
            print "  <tr>";
            print "    <th>VRM</th>";
            print "    <th>Permit Group</th>";
            print "    <th>Entry Time</th>";
            print "    <th>Exit Time</th>";
            print "    <th>Duration</th>";
            print "  </tr>";
            foreach($new_platelist as $wlPlate) {
                if ($myArray[$wlPlate][0][2] == $dayCheck) { 
                print "<tr>";
                print "<td>$wlPlate</td>";
                if (isset($myArray[$wlPlate])) {
                    print "<td>".$staticWhitelistArray[$wlPlate]['groupname']."</td>";
                    print "<td>".$myArray[$wlPlate][0][0]."</td>";
                    print "<td>".$myArray[$wlPlate][0][1]."</td>";
                    print "<td>".$myArray[$wlPlate][0][3]."</td>";
                }
                else {
                    print "<td>Vehicle Not Seen</td>";
                    print "<td>Vehicle Not Seen</td>";
                    print "<td>Vehicle Not Seen</td>";
                }
                print "</tr>";
                }
            }
            print "</table>";
            $startLoopDate = $startLoopDate + $oneDay;
        }
    }
} 

© Programmers or respective owner

Related posts about php